package com.epam.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.dao.DAOFactory;
import com.epam.dao.DAOFactory.DAOType;
import com.epam.dao.intf.DAO;
import com.epam.dao.intf.ITouristVoucherDAO;
import com.epam.database.ConnectionPool;
import com.epam.entity.AbstractEntity;
import com.epam.entity.Hotel;
import com.epam.entity.TouristVoucher;
import com.epam.entity.TouristVoucher.Country;
import com.epam.entity.TouristVoucher.Transport;
import com.epam.entity.TouristVoucher.Type;

public class TouristVoucherDAO implements ITouristVoucherDAO {

	private static final Logger LOG = Logger.getLogger(TouristVoucherDAO.class);
	private static final String INSERT_TOURIST_VOUCHER = "INSERT INTO tourist_vouchers (country, duration, type, transport, hotel, cost, last_minute) values (?,?,?,?,?,?,?)";
	private static final String FIND_TOURIST_VOUCHER = "SELECT * FROM tourist_vouchers WHERE voucher_id = ?";
	private static final String FIND_TOURIST_VOUCHERS = "SELECT * FROM tourist_vouchers WHERE available = true";
	private static final String UPDATE_AVAILABILITY = "UPDATE tourist_vouchers SET available = ? WHERE voucher_id = ?";
	private static final String CHECK_AVAILABILITY = "SELECT available FROM tourist_vouchers WHERE voucher_id = ?";

	@Override
	public void insert(AbstractEntity entity) {

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(INSERT_TOURIST_VOUCHER);

			TouristVoucher voucher = (TouristVoucher) entity;

			st.setString(1, voucher.getCountry().toString());
			st.setInt(2, voucher.getDuration());
			st.setString(3, voucher.getType().toString());
			st.setString(4, voucher.getTransport().toString());
			st.setInt(6, voucher.getCost());
			st.setBoolean(7, voucher.isLastMinute());
			st.setInt(5, voucher.getHotel().getHotelId());
			LOG.info("Voucher inserted " + voucher.toString());
			st.execute();

			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}

	}

	@Override
	public TouristVoucher findByID(int id) {

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_TOURIST_VOUCHER);
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();

			if (rs.isBeforeFirst()) {
				rs.next();

				TouristVoucher voucher = construct(rs);

				ConnectionPool.INSTANCE.retreiveConnection(connection);
				return voucher;
			}
			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}

		return null;

	}

	@Override
	public List<AbstractEntity> findAll() {
		List<AbstractEntity> vouchers = new ArrayList<AbstractEntity>();
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_TOURIST_VOUCHERS);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {

				TouristVoucher voucher = construct(rs);

				vouchers.add(voucher);
			}
			ConnectionPool.INSTANCE.retreiveConnection(connection);
			return vouchers;

		} catch (SQLException e) {
			LOG.error(e);
		}
		return null;
	}

	@Override
	public void updateAvailability(int voucher_id, boolean available) {

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(UPDATE_AVAILABILITY);
			st.setBoolean(1, available);
			st.setInt(2, voucher_id);
			st.execute();

			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}

	}

	@Override
	public boolean isAvailable(int voucher_id) {
		Connection connection = ConnectionPool.INSTANCE.takeConnection();
		boolean result = false;
		try {
			PreparedStatement st = connection
					.prepareStatement(CHECK_AVAILABILITY);
			st.setInt(1, voucher_id);
			ResultSet rs = st.executeQuery();

			rs.next();
			if (rs.getString("available").equals("t")) {
				result = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			LOG.error(e);
		}
		ConnectionPool.INSTANCE.retreiveConnection(connection);
		return result;

	}

	@Override
	public void updateAttribute(String attribute, int id, Object value) {
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement("UPDATE tourist_vouchers SET "
							+ attribute + " = ? WHERE voucher_id = ?");
			st.setObject(1, value);
			st.setInt(2, id);

			st.executeUpdate();
			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}

	}

	private TouristVoucher construct(ResultSet rs) {
		DAO hotelDAO = DAOFactory.INSTANCE.getDAO(DAOType.HOTEL);

		int voucher_id = 0;
		Country country = null;
		int duration = 0;
		Type type = null;
		Transport transport = null;
		Hotel hotel = null;
		int cost = 0;
		boolean lastMinute = false;

		try {
			voucher_id = rs.getInt("voucher_id");
			country = Enum.valueOf(TouristVoucher.Country.class,
					rs.getString("country"));
			duration = rs.getInt("duration");
			type = Enum
					.valueOf(TouristVoucher.Type.class, rs.getString("type"));
			transport = Enum.valueOf(TouristVoucher.Transport.class,
					rs.getString("transport"));
			hotel = (Hotel) hotelDAO.findByID(rs.getInt("hotel"));
			cost = rs.getInt("cost");
			lastMinute = rs.getBoolean("last_minute");
		} catch (SQLException e) {
			LOG.error(e);
		}

		TouristVoucher voucher = new TouristVoucher.TouristVoucherBuilder()
				.id(voucher_id).country(country).duration(duration).type(type)
				.transport(transport).hotel(hotel).cost(cost)
				.lastMinute(lastMinute).build();
		return voucher;
	}

	@Override
	public List<AbstractEntity> findAllPaginated(int offset, int numberOfRecords) {
		Connection connection = ConnectionPool.INSTANCE.takeConnection();
		List<AbstractEntity> vouchers = new ArrayList<AbstractEntity>();

		try {
			PreparedStatement st = connection.prepareStatement(
					"SELECT * FROM tourist_vouchers OFFSET ? LIMIT ?",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, offset);
			st.setInt(2, numberOfRecords);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {
				TouristVoucher tv = construct(rs);
				vouchers.add(tv);
			}

		} catch (SQLException e) {
			LOG.error(e);
		}
		ConnectionPool.INSTANCE.retreiveConnection(connection);
		System.out.println(vouchers.toString());
		return vouchers;

	}
}
